package de.wittekind.bundesjugendspiele.server.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import de.wittekind.bundesjugendspiele.server.DatabaseConnectionManager;
import de.wittekind.bundesjugendspiele.server.interfaces.Discipline;
import de.wittekind.bundesjugendspiele.server.interfaces.Riege;
import de.wittekind.bundesjugendspiele.server.interfaces.Schueler;
import de.wittekind.bundesjugendspiele.server.interfaces.Try;

/**
 * @author Sascha Wegener
 * 
 * @version 17.04.2013
 */
public class SQLRiege implements Riege {

	private int id;

	public SQLRiege(int id) throws IOException {
		this.id = id;
	}

	@Override
	public Schueler getSchueler(int id) throws IOException {
		return new SQLSchueler(id);
	}

	@Override
	public void addSchueler(Schueler t) throws IOException {
		try {
			int sid = t.getID();
			if (!(t instanceof SQLSchueler) || sid == -1) {
				SQLSchueler sch = new SQLSchueler(newSchueler());
				sch.setBirthday(t.getBirthday());
				sch.setFirstName(t.getFirstName());
				sch.setLastName(t.getLastName());
				sch.setGender(t.getGender());
				sch.setForm(t.getForm());
				for (Try oldTry : t.getTries()) {
					Try newTry = sch.createNewTry();
					newTry.setDiscipline(oldTry.getDiscipline());
					newTry.setUnit(oldTry.getUnit());
					newTry.setValue(oldTry.getValue());
				}
			} else {
				Connection c = DatabaseConnectionManager.getConnection();
				Statement s = c.createStatement();
				String sql = String.format(
						"update schueler set r_id=%s where id=%s", id, sid);
				s.executeUpdate(sql);
				s.close();
			}
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());

		}
	}

	@Override
	public void removeSchueler(int id) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format(
					"update schueler set r_id=0 where id=%s", id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {

			throw new IOException(ex.getMessage());

		}
	}

	@Override
	@Deprecated
	public void setDiscipline(int id, Discipline d) throws IOException {
		// TODO Auto-generated method stub

	}

	@Override
	@Deprecated
	public int getSchuelerAmount() throws IOException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int[] getSchuelerIDs() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select id from schueler where r_id=" + id +" order by nachname asc";
			ResultSet rs = s.executeQuery(sql);
			java.util.List<Integer> ids = new java.util.ArrayList<Integer>();
			while (rs.next()) {
				ids.add(rs.getInt(1));
			}
			s.close();
			int[] ids2 = new int[ids.size()];
			for (int i = 0; i < ids2.length; i++) {
				ids2[i] = ids.get(i);
			}
			return ids2;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());

		}
	}

	@Override
	public String getRiegenFuehrer() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = "select fuehrer from riege where id=" + id;
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			String rf = rs.getString(1);
			s.close();
			return rf;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());
		}

	}

	@Override
	public void setRiegenFuehrer(String nFuehrer) throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();
			Statement s = c.createStatement();
			String sql = String.format(
					"update riege set fuehrer='%s' where id=%s", nFuehrer, id);
			s.executeUpdate(sql);
			s.close();
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());

		}
	}

	@Override
	public int newSchueler() throws IOException {
		try {
			Connection c = DatabaseConnectionManager.getConnection();

			// Neue ID bestimmen
			Statement s = c.createStatement();
			String sql = "select max(id) from schueler";
			ResultSet rs = s.executeQuery(sql);
			rs.next();
			int newID = rs.getInt(1) + 1;
			s.close();

			// Schueler erzeugen
			s = c.createStatement();
			sql = String.format(
					"insert into schueler (id,r_id) values (%s,%s)", newID, id);
			s.executeUpdate(sql);
			s.close();
			return newID;
		} catch (SQLException ex) {
			throw new IOException(ex.getMessage());

		}

	}

	@Override
	public int getID() throws IOException {
		return id;
	}

	@Override
	public int[] getActiveSchuelerIDs() throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

}
